package com.neuedu.jdbc.dao;

import com.neuedu.jdbc.DBUtils;
import com.neuedu.jdbc.po.Account;
import com.neuedu.jdbc.po.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/***
 * 项目名称:  MyJDBC 
 * 包：  com.neuedu.jdbc.dao 
 * date:   2025/11/7   11:30  
 * 描述:    Dao  Date Access Object
 *              封装了一些常见的 CRUD(增删改查)的操作
 *                  selectByID
 *                  selectlist
 *                  updateById
 *                  deleteById
 *                  isnert()
 *  @author : 张金山
 *  作者博客:   https://blog.fulfill.com.cn
 */
public class AccountDao extends BaseDao {

    private Connection conn;


    public AccountDao(Connection conn) {
        this.conn = conn;
    }

    /**
         * 根据主键查询单条结果
         *
         * @param id
         * @return
         */
    public Account selectById(long id) {




        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        Account po = null;


        // 8.x +
        try {
            // 1 加载驱动  建立连接
            conn = DBUtils.getConnection();

            // 3 创建语句块stmt
            // stmt = conn.createStatement();
            // 4 执行sql 语句
            String sql = "select  *   from account where id =  ? ";

            stmt = conn.prepareStatement(sql);

            //绑定变量
            stmt.setLong(1,id);
            rs = stmt.executeQuery();


            // 解析结果集
            if (rs.next()) {

                po = new Account(
                        rs.getLong("id"),
                        rs.getString("name"),
                        rs.getDouble("amount")
                   );

            }


        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭资源
            super.close(conn, stmt, rs);

        }


        return po;
    }


    /**
     * 根据条件查询集合
     *
     * @param
     * @return
     */
    public List<Account> selectList() {




        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        List list = new ArrayList();

        // 旧版  5.xx-
        // Class.forName("com.mysql.jdbc.Driver");
        // 8.x +
        try {
            // 1 加载驱动  建立连接
            conn = DBUtils.getConnection();

            // 3 创建语句块stmt
            // 4 执行sql 语句
            StringBuffer sql = new StringBuffer("select *  from account ");



            stmt = conn.prepareStatement(sql.toString());





            System.out.println("SQL: " + sql.toString());

            rs = stmt.executeQuery();



            // 解析结果集
            while (rs.next()) {

                Account po = new Account(
                        rs.getLong("id"),
                        rs.getString("name"),
                        rs.getDouble("amount")
                );
                list.add(po);


            }


        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭资源
            super.close(conn, stmt, rs);

        }

        return list;
    }


    public int updateById(Account po) {


        StringBuilder sql = new StringBuilder();
        sql.append("  update 					");
        sql.append("    `his`.`account` 				");
        sql.append("  set			");
        sql.append("    `name` = ?  ,		");
        sql.append("    `amount` = ?  		");
         sql.append("   WHERE `id` =  ? 		"   );

        int count = 0;


        Connection connection = null;
        PreparedStatement stmt = null;
        try {
            // 1 加载驱动  建立连接

            // connection = this.getConnection();


            // 创建sttm
            stmt = conn.prepareStatement(sql.toString());

            //  执行SQL语句

            // count = stmt.executeUpdate(sql);

            //绑定变量

            int i = 1;
            stmt.setObject(i++, po.getName());
            stmt.setObject(i++, po.getAmount());
            stmt.setObject(i++, po.getId());

            count = stmt.executeUpdate();


        }  catch (SQLException e) {
            e.printStackTrace();
        } finally {

            // 关闭资源
            this.close(null, stmt, null);
        }

        return count;
    }

    public int insert(Account po) {
        String sql = "   INSERT INTO `his`.`account` (  `name`,  `amount` ) VALUES  ( ? , ?) ";
        int count = super.executeUpdate(sql,
                po.getName(),
                po.getAmount()
        );
        return count;
    }

    public int deleteById(long id) {

        String sql = "delete from account where id = ?  ";
        int count = super.executeUpdate(sql,id);
        return count;

    }





    public static void main(String[] args) {

        // AccountDao accountDao = new AccountDao();


        //1 测试增加两个账户
        // Account account1 = new Account();
        // account1.setName("张三");
        // account1.setAmount(5000.0);
        // int count1 = accountDao.insert(account1);
        //
        // System.out.println("count1 = " + count1);
        //
        // Account account2 = new Account();
        // account2.setName("李四");
        // account2.setAmount(5000.0);
        // int count2 = accountDao.insert(account2);
        // System.out.println("count2 = " + count2);


        //2 遍历
        // accountDao.selectList().forEach(System.out::println);



    }
}
